<?php

namespace App\Http\Controllers\User;

use App\Models\Log\FinanceLog;
use App\Models\Log\DiamondLog;
use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Auth;
use App\Models\Platform;
use App\Models\Agent\AgentTransfer;

class LogController extends Controller
{
    public function index(Request $request)
    {
        if ($request->ajax()) {

            $rows = $request->get("rows");

            $FinanceLog = $this->filters($request, new FinanceLog());

            $paginator = $FinanceLog->orderBy($request->get("sidx"), $request->get("sord"))->paginate($rows);            $rows = [];
            foreach ($paginator as $page) {
                $page->time = substr($page->time, 0, 18);
                $rows[] = $page;
            }

            $data = [
                'rows' => $rows,
                'total'=> $paginator->lastPage(),
                'page' => $paginator->currentPage(),
                'records' => $paginator->count(),
            ];

            return response()->json($data);

        } else {
            return view('log/money/index');
        }
    }

    public function diamond(Request $request)
    {
        if ($request->ajax()) {

            $DiamondLog = DiamondLog::where("item_type", 1);

            $DiamondLog = $this->filters($request, $DiamondLog);

//             $admin_id = Auth::user()->user_id;
//             if (!UserPlatform::isAll($admin_id)) {
//                 $DiamondLog = $DiamondLog->join("t_users", "user_id", "=", "userid")
//                 ->whereIn("t_users.SPID", UserPlatform::ids($admin_id));
//             }

            $paginator = $DiamondLog->orderBy($request->get("sidx"), $request->get("sord"))->paginate($request->get("rows"));
    
            $rows = [];
            foreach ($paginator as $page) {
                $page->time = substr($page->time, 0, 18);
                $rows[] = $page;
            }
    
            $data = [
                'rows' => $rows,
                'total'=> $paginator->lastPage(),
                'page' => $paginator->currentPage(),
                'records' => $paginator->count(),
            ];
    
            return response()->json($data);
    
        } else {
            return view('log/diamond/index');
        }
    }

    public function diamond1(Request $request)
    {
        if ($request->ajax()) {

            $rows = $request->get("rows");

            $DiamondLog = $this->filters($request, new DiamondLog());
            $DiamondLog = $DiamondLog
            ->where("item_type", 1)
            ->where("invoker", "后台修改");

            $paginator = $DiamondLog->orderBy($request->get("sidx"), $request->get("sord"))->paginate($rows);

            $rows = [];
            foreach ($paginator as $page) {
                $page->time = substr($page->time, 0, 18);
                $rows[] = $page;
            }

            $data = [
                'rows' => $rows,
                'total'=> $paginator->lastPage(),
                'page' => $paginator->currentPage(),
                'records' => $paginator->count(),
            ];

            return response()->json($data);

        } else {
            return view('log/diamond/index1');
        }
    }

    public function diamond2(Request $request)
    {
        if ($request->ajax()) {

            $begin_time = $request->get("begin_time");
            $end_time = $request->get("end_time");

            $DiamondLog = $this->filters($request, new DiamondLog());
            
            $DiamondLog = $DiamondLog
            ->where("item_type", 1)
            ->where("invoker", "后台转账");
            
            if ($begin_time) {
                $DiamondLog = $DiamondLog->where("ope_time", ">=", $begin_time);
            }
            if ($end_time) {
                $DiamondLog = $DiamondLog->where("ope_time", "<=", $end_time . " 23:59:59");
            }

//             $admin_id = Auth::user()->user_id;
//             if (!UserPlatform::isAll($admin_id)) {
//                 $spids = UserPlatform::ids($admin_id);
//                 $DiamondLog = $DiamondLog->join("t_users", function($join) use ($spids) {
//                     $join->on("userid", "=", "userid");
//                 });
//             }

            $paginator = $DiamondLog->orderBy($request->get("sidx"), $request->get("sord"))->paginate($request->get("rows"));

            $sum = $this->filters($request, new DiamondLog())
            ->select(DB::raw("sum(count) as count"))
            ->where("item_type", 1)
            ->where("invoker", "后台转账");

            if ($begin_time) {
                $sum = $sum->where("ope_time", ">=", $begin_time);
            }
            if ($end_time) {
                $sum = $sum->where("ope_time", "<=", $end_time . " 23:59:59");
            }
            
            $sum = $sum->first()->count;
            
            $rows = [];
            foreach ($paginator as $page) {
                $page->time = substr($page->time, 0, 18);
                $page->sum = $sum;
                $rows[] = $page;
            }

            $data = [
                'rows' => $rows,
                'total'=> $paginator->lastPage(),
                'page' => $paginator->currentPage(),
                'records' => $paginator->count(),
            ];

            return response()->json($data);

        } else {
            return view('log/diamond/index2');
        }
    }
    
    public function diamond3(Request $request)
    {
        if ($request->ajax()) {
    
            $rows = $request->get("rows");
    
            $DiamondLog = $this->filters($request, new DiamondLog());
            $DiamondLog = $DiamondLog
            ->where("item_type", 1)
            ->where("invoker", "代理转账");

//             $admin_id = Auth::user()->user_id;
//             if (!UserPlatform::isAll($admin_id)) {
//                 $spids = UserPlatform::ids($admin_id);
//                 $DiamondLog = $DiamondLog->join("t_users", function($join) use ($spids) {
//                     $join->on("userid", "=", "userid")
//                     ->whereIn("t_users.SPID", $spids);
//                 });
//             }

            $paginator = $DiamondLog->orderBy($request->get("sidx"), $request->get("sord"))->paginate($rows);
    
            $rows = [];
            foreach ($paginator as $page) {
                $page->time = substr($page->time, 0, 18);
                $rows[] = $page;
            }
    
            $data = [
                'rows' => $rows,
                'total'=> $paginator->lastPage(),
                'page' => $paginator->currentPage(),
                'records' => $paginator->count(),
            ];
    
            return response()->json($data);
    
        } else {
            return view('log/diamond/index3');
        }
    }

    public function gold(Request $request)
    {
        if ($request->ajax()) {

            $DiamondLog = DiamondLog::where("item_type", 2);

            $DiamondLog = $this->filters($request, $DiamondLog);

            $paginator = $DiamondLog->orderBy($request->get("sidx"), $request->get("sord"))->paginate($request->get("rows"));

            $rows = [];
            foreach ($paginator as $page) {
                $page->time = substr($page->time, 0, 18);
                $rows[] = $page;
            }

            $data = [
                'rows' => $rows,
                'total'=> $paginator->lastPage(),
                'page' => $paginator->currentPage(),
                'records' => $paginator->count(),
            ];

            return response()->json($data);

        } else {
            return view('log/gold/index');
        }
    }

    public function gold1(Request $request)
    {
        if ($request->ajax()) {

            $rows = $request->get("rows");

            $DiamondLog = $this->filters($request, new DiamondLog());
            $DiamondLog = $DiamondLog
            ->where("item_type", 2)
            ->where("invoker", "后台修改");

            $paginator = $DiamondLog->orderBy($request->get("sidx"), $request->get("sord"))->paginate($rows);

            $rows = [];
            foreach ($paginator as $page) {
                $page->time = substr($page->time, 0, 18);
                $rows[] = $page;
            }

            $data = [
                'rows' => $rows,
                'total'=> $paginator->lastPage(),
                'page' => $paginator->currentPage(),
                'records' => $paginator->count(),
            ];

            return response()->json($data);

        } else {
            return view('log/gold/index1');
        }
    }

    public function gold2(Request $request)
    {
        if ($request->ajax()) {

            $begin_time = $request->get("begin_time");
            $end_time = $request->get("end_time");

            $DiamondLog = $this->filters($request, new DiamondLog());

            $DiamondLog = $DiamondLog
            ->where("item_type", 2)
            ->where("invoker", "后台转账");

            if ($begin_time) {
                $DiamondLog = $DiamondLog->where("ope_time", ">=", $begin_time);
            }
            if ($end_time) {
                $DiamondLog = $DiamondLog->where("ope_time", "<=", $end_time . " 23:59:59");
            }

            $paginator = $DiamondLog->orderBy($request->get("sidx"), $request->get("sord"))->paginate($request->get("rows"));

            $sum = $this->filters($request, new DiamondLog())
            ->select(DB::raw("sum(count) as count"))
            ->where("item_type", 2)
            ->where("invoker", "后台转账");

            if ($begin_time) {
                $sum = $sum->where("ope_time", ">=", $begin_time);
            }
            if ($end_time) {
                $sum = $sum->where("ope_time", "<=", $end_time . " 23:59:59");
            }

            $sum = $sum->first()->count;

            $rows = [];
            foreach ($paginator as $page) {
                $page->time = substr($page->time, 0, 18);
                $page->sum = $sum;
                $rows[] = $page;
            }

            $data = [
                'rows' => $rows,
                'total'=> $paginator->lastPage(),
                'page' => $paginator->currentPage(),
                'records' => $paginator->count(),
            ];

            return response()->json($data);

        } else {
            return view('log/gold/index2');
        }
    }

    public function gold3(Request $request)
    {
        if ($request->ajax()) {

            $rows = $request->get("rows");

            $DiamondLog = $this->filters($request, new DiamondLog());
            $DiamondLog = $DiamondLog
                ->where("item_type", 2)
                ->where("invoker", "代理转账");

            $paginator = $DiamondLog->orderBy($request->get("sidx"), $request->get("sord"))->paginate($rows);

            $rows = [];
            foreach ($paginator as $page) {
                $page->time = substr($page->time, 0, 18);
                $rows[] = $page;
            }

            $data = [
                'rows' => $rows,
                'total'=> $paginator->lastPage(),
                'page' => $paginator->currentPage(),
                'records' => $paginator->count(),
            ];

            return response()->json($data);
        } else {
            return view('log/gold/index3');
        }
    }

    public function rake(Request $request)
    {
        $date_func = "CONVERT(VARCHAR(100), time, 23)";

        if ($request->ajax()) {

            $this->validate($request, [
                'begin_time' => 'date',
                'end_time' => 'date',
            ]);

            $group = $request->get("group");

            $orderBy = $request->get("sidx");
            $gstr = "'-'";
            $dstr = "'-'";
            if($group == "date") {
                $column = $date_func;
                $dstr = $date_func;
                if ($orderBy == "spid") {
                    $orderBy = $date_func;
                }
            }
            if($group == "spid") {
                $column = "spid";
                $gstr = "spid";
                if ($orderBy == $date_func) {
                    $orderBy = $gstr;
                }
            }
            if($group == "dateandspid") {
                $dstr = $date_func;
                $gstr = "spid";
                $column = "{$date_func} , spid";
            }

            $begin_time = $request->get("begin_time");
            $end_time = $request->get("end_time");

            if (!$begin_time) {
                $begin_time = "1970-01-01";
            }

            if (!$end_time) {
                $end_time = date("Y-m-d") . " 23:59:59";
            } else {
                $end_time = $end_time . " 23:59:59";
            }

            $acc = DB::connection('log')->table("finance_logs")
            ->select(DB::raw(
                "{$gstr} AS spid ,
                {$dstr} AS [{$date_func}] ,
                COUNT(1) AS [COUNT(1)] ,
                SUM(count) AS [SUM(count)]"
            ))
            ->where("item_type", 5)
            ->whereBetween("time", [$begin_time, $end_time]);

            $acc = $acc->groupBy(DB::raw($column));

            $paginator = $acc->orderBy(DB::raw($orderBy), $request->get("sord"))->paginate($request->get("rows"));

            $platforms = Platform::get();

            $rows = [];
            foreach ($paginator as $page) {

                foreach ($platforms as $p) {
                    if ($p->id == $page->spid) {
                        $page->spid = "{$p->name}($p->id)";
                    }
                }
                $rows[] = $page;
            }

            $data = [
                'rows' => $rows,
                'total'=> $paginator->lastPage(),
                'page' => $paginator->currentPage(),
                'records' => $paginator->count(),
            ];

            return response()->json($data);
        } else {
            return view('log/money/rake', ['date_func' => $date_func]);
        }
    }

    public function group(Request $request)
    {
        $date_func = "CONVERT(VARCHAR(100), time, 23)";

        if ($request->ajax()) {

            $this->validate($request, [
                'begin_time' => 'date',
                'end_time' => 'date',
            ]);

            $group = $request->get("group");

            $orderBy = $request->get("sidx");
            $gstr = "'-'";
            $dstr = "'-'";
            if($group == "date") {
                $column = $date_func;
                $dstr = $date_func;
                if ($orderBy == "spid") {
                    $orderBy = $date_func;
                }
            }
            if($group == "spid") {
                $column = "spid";
                $gstr = "spid";
                if ($orderBy == $date_func) {
                    $orderBy = $gstr;
                }
            }
            if($group == "dateandspid") {
                $dstr = $date_func;
                $gstr = "spid";
                $column = "{$date_func} , spid";
            }

            $begin_time = $request->get("begin_time");
            $end_time = $request->get("end_time");

            if (!$begin_time) {
                $begin_time = "1970-01-01";
            }

            if (!$end_time) {
                $end_time = date("Y-m-d") . " 23:59:59";
            } else {
                $end_time = $end_time . " 23:59:59";
            }

            $acc = DB::connection('log')->table("finance_logs")
            ->select(DB::raw(
                "{$gstr} AS spid ,
                {$dstr} AS [{$date_func}] ,
                SUM(CASE [type] WHEN 1 then win else 0 end) AS [type1] ,
                SUM(CASE [type] WHEN 2 then win else 0 end) AS [type2] ,
                SUM(CASE [type] WHEN 3 then win else 0 end) AS [type3] , 
                SUM(CASE [type] WHEN 4 then win else 0 end) AS [type4] ,
                SUM(CASE [type] WHEN 5 then win else 0 end) AS [type5] ,
                SUM(CASE [type] WHEN 6 then win else 0 end) AS [type6]"
            ))
            ->whereBetween("time", [$begin_time, $end_time]);

            $acc = $acc->groupBy(DB::raw($column));
            $paginator = $acc->orderBy(DB::raw($orderBy), $request->get("sord"))->paginate($request->get("rows"));

            $platforms = Platform::get();

            $rows = [];
            foreach ($paginator as $page) {

                foreach ($platforms as $p) {
                    if ($p->id == $page->spid) {
                        $page->spid = "{$p->name}($p->id)";
                    }
                }

                $page->sumn = $page->type1 + $page->type2 + $page->type3 + $page->type4 + $page->type5 + $page->type6;
                $rows[] = $page;
            }

            $data = [
                'rows' => $rows,
                'total'=> $paginator->lastPage(),
                'page' => $paginator->currentPage(),
                'records' => $paginator->count(),
            ];

            return response()->json($data);
        } else {
            return view('log/money/group', ['date_func' => $date_func]);
        }
    }

    public function rmb(Request $request)
    {
        if ($request->ajax()) {

            $rows = $request->get("rows");

            $tran = $this->filters($request, new DiamondLog());
            $tran = $tran
            ->where("item_type", 1)
            ->where("invoker", "后台修改");

            $paginator = $tran->orderBy($request->get("sidx"), $request->get("sord"))->paginate($rows);

            $rows = [];
            foreach ($paginator as $page) {
                $page->time = substr($page->time, 0, 18);
                $rows[] = $page;
            }

            $data = [
                'rows' => $rows,
                'total'=> $paginator->lastPage(),
                'page' => $paginator->currentPage(),
                'records' => $paginator->count(),
            ];

            return response()->json($data);
        } else {
            return view('log/diamond/index1');
        }
        
    }
}
